package com.rymcu.forest.rag;

import com.rymcu.forest.entity.Article;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * SuperSQL集成服务 - 实现自然语言转SQL查询
 * 用于RAG功能的数据库查询层
 */
@Slf4j
@Service
public class SuperSQLService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 将自然语言问题转换为SQL查询并执行
     *
     * @param question 用户自然语言问题
     * @return 查询结果和相关文章信息
     */
    public RAGQueryResult queryByNaturalLanguage(String question) {
        try {
            // 1. 分析问题类型并生成对应SQL
            String sql = generateSQLFromQuestion(question);
            log.info("Generated SQL: {}", sql);

            // 2. 执行SQL查询
            List<Map<String, Object>> queryResults = jdbcTemplate.queryForList(sql);

            // 3. 提取相关文章ID
            Set<Long> articleIds = extractArticleIds(queryResults);

            // 4. 获取完整的文章信息
            List<Article> articles = getArticlesByIds(articleIds);

            // 5. 构建RAG查询结果
            return RAGQueryResult.builder()
                    .question(question)
                    .sql(sql)
                    .queryResults(queryResults)
                    .articles(articles)
                    .context(buildContext(articles))
                    .build();

        } catch (Exception e) {
            log.error("SuperSQL查询失败: {}", e.getMessage(), e);
            return RAGQueryResult.builder()
                    .question(question)
                    .error("查询失败: " + e.getMessage())
                    .build();
        }
    }

    /**
     * 根据自然语言问题生成SQL查询
     */
    private String generateSQLFromQuestion(String question) {
        String lowerQuestion = question.toLowerCase();

        // 文章搜索相关
        if (lowerQuestion.contains("文章") || lowerQuestion.contains("博客") || lowerQuestion.contains("教程")) {
            if (lowerQuestion.contains("最新") || lowerQuestion.contains("最近")) {
                return "SELECT * FROM forest_article WHERE article_status = '0' ORDER BY created_time DESC LIMIT 10";
            } else if (lowerQuestion.contains("热门") || lowerQuestion.contains("受欢迎")) {
                return "SELECT * FROM forest_article WHERE article_status = '0' ORDER BY article_view_count DESC LIMIT 10";
            } else if (lowerQuestion.contains("标签") || lowerQuestion.contains("tag")) {
                return "SELECT * FROM forest_article WHERE article_status = '0' AND article_tags LIKE '%技术%' ORDER BY article_thumbs_up_count DESC LIMIT 10";
            }
        }

        // 特定主题查询
        if (lowerQuestion.contains("编程") || lowerQuestion.contains("代码")) {
            return "SELECT a.*, ac.article_content FROM forest_article a " +
                   "JOIN forest_article_content ac ON a.id = ac.id_article " +
                   "WHERE a.article_status = '0' AND " +
                   "(a.article_title LIKE '%编程%' OR a.article_title LIKE '%代码%' OR ac.article_content LIKE '%编程%') " +
                   "ORDER BY a.article_view_count DESC LIMIT 5";
        }

        // 默认查询最新文章
        return "SELECT * FROM forest_article WHERE article_status = '0' ORDER BY created_time DESC LIMIT 5";
    }

    /**
     * 从查询结果中提取文章ID
     */
    private Set<Long> extractArticleIds(List<Map<String, Object>> queryResults) {
        Set<Long> articleIds = new HashSet<>();
        for (Map<String, Object> result : queryResults) {
            if (result.containsKey("id")) {
                articleIds.add(((Number) result.get("id")).longValue());
            }
        }
        return articleIds;
    }

    /**
     * 根据ID获取文章信息
     */
    private List<Article> getArticlesByIds(Set<Long> articleIds) {
        if (articleIds.isEmpty()) {
            return new ArrayList<>();
        }

        try {
            // 构建IN查询
            String idsStr = articleIds.toString().replace("[", "(").replace("]", ")");
            String sql = String.format("SELECT * FROM forest_article WHERE id IN %s", idsStr);

            return jdbcTemplate.query(sql, (rs, rowNum) -> {
                Article article = new Article();
                article.setIdArticle(rs.getLong("id"));
                article.setArticleTitle(rs.getString("article_title"));
                article.setArticlePreviewContent(rs.getString("article_preview_content"));
                article.setArticleTags(rs.getString("article_tags"));
                article.setArticlePermalink(rs.getString("article_permalink"));
                article.setArticleViewCount(rs.getInt("article_view_count"));
                article.setArticleThumbsUpCount(rs.getInt("article_thumbs_up_count"));
                article.setCreatedTime(rs.getTimestamp("created_time"));
                return article;
            });
        } catch (Exception e) {
            log.error("查询文章信息失败: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 构建AI回答上下文
     */
    private String buildContext(List<Article> articles) {
        if (articles.isEmpty()) {
            return "没有找到相关文章信息。";
        }

        StringBuilder context = new StringBuilder();
        context.append("基于以下博客文章信息回答问题：\n\n");

        for (int i = 0; i < articles.size(); i++) {
            Article article = articles.get(i);
            context.append(String.format("文章%d：%s\n", i + 1, article.getArticleTitle()));
            context.append(String.format("摘要：%s\n", article.getArticlePreviewContent()));
            context.append(String.format("标签：%s\n", article.getArticleTags()));
            context.append(String.format("链接：%s\n\n", article.getArticlePermalink()));
        }

        return context.toString();
    }
}